package com.blue.waffle.iwc.domains.helpers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Random;

import com.blue.waffle.iwc.domains.Difficulty;
import com.blue.waffle.iwc.domains.Level;
import com.blue.waffle.iwc.domains.Word;
import com.blue.waffle.iwc.utils.LruCache;

public class LevelHelper implements DataHelper<Level> {

	private Connection connection;
	
	private static final String TABLE_NAME="level";
	
	private PreparedStatement selectStatement;
	private PreparedStatement updateStatement;
	private PreparedStatement insertStatement;
	private PreparedStatement deleteStatement;
	
	private static final String SELECT_ALL_SQL="SELECT * FROM "+TABLE_NAME;
	
	private static final String SELECT_ALL_DIFFICULTY_LEVEL_SQL="SELECT * FROM "+TABLE_NAME + " WHERE difficulty_id=?";
	
	private static LruCache<Integer, Level> objectCache;
	
	private static final String INSERT_SQL="INSERT INTO "+TABLE_NAME+" (word_id, difficulty_id ,title) VALUES (?,?,?)";
	private static final String UPDATE_SQL="UPDATE "+TABLE_NAME+" SET word_id=?, difficulty_id=?, title=? WHERE id=?";
	private static final String DELETE_SQL="DELETE FROM "+TABLE_NAME+" WHERE id=?";
	private static final String COUNT_SQL="SELECT count(*) FROM "+TABLE_NAME;
	private static final String COUNT_DIFFICULTY_LEVEL_SQL="SELECT count(*) FROM "+TABLE_NAME + " WHERE difficulty_id=?";
	private static final String SELECT_ALL_WITH_WORD="SELECT t.*, w.word,w.definition FROM "+TABLE_NAME+" t INNER JOIN word w ON t.word_id=w.id WHERE t.difficulty_id=?";
	
	public LevelHelper(Connection connection){
		this.connection=connection;
		if (objectCache==null) {
			objectCache=new LruCache<Integer, Level>(10);
		}
	}
	
	
	@Override
	public List<Level> selectAll() throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(SELECT_ALL_SQL);
		List<Level> list = new ArrayList<Level>();
		while(resultSet.next()) {
			list.add(getObject(resultSet));
		}
		resultSet.close();
		return list;
	}
	
	/**
	 * Returns all the {@link Level levels} found with the supplied {@link Difficulty difficulty}.
	 * @param difficulty
	 * @return
	 * @throws SQLException
	 */
	public List<Level> selectAll(Difficulty difficulty) throws SQLException {
		selectStatement = connection.prepareStatement(SELECT_ALL_DIFFICULTY_LEVEL_SQL);
		selectStatement.setInt(1, difficulty.getId());
		ResultSet resultSet = selectStatement.executeQuery();
		List<Level> list = new ArrayList<Level>();
		while(resultSet.next()) {
			list.add(getObject(resultSet));
		}
		resultSet.close();
		selectStatement.close();
		return list;
	}
	
	public List<Level> selectAll(Difficulty difficulty, boolean randomize) throws SQLException{
		List<Level> levels=selectAll(difficulty);
		if (randomize) {
			if (levels!=null) {
				Collections.shuffle(levels);
			}
		}
		return levels;
	}
	
	public List<Level> selectAll(Difficulty difficulty, long randomSeed) throws SQLException{
		List<Level> levels=selectAll(difficulty);
		
		if (levels != null) {
			Collections.shuffle(levels, new Random(randomSeed));
		}
		
		return levels;
	}
	
	public List<Level> selectAllWithWord(Difficulty difficulty, long randomSeed) throws SQLException{
		selectStatement = connection.prepareStatement(SELECT_ALL_WITH_WORD);
		selectStatement.setInt(1, difficulty.getId());
		ResultSet resultSet = selectStatement.executeQuery();
		List<Level> list = new ArrayList<Level>();
		while(resultSet.next()) {
			list.add(getObject(resultSet,true));
		}
		resultSet.close();
		selectStatement.close();
		if (list!=null && !list.isEmpty()) {
			Collections.shuffle(list,new Random(randomSeed));
		}
		return list;
	}

	@Override
	public List<Level> select(String sql, Object[] para) throws SQLException {
		return null;
	}

	/**
	 * Takes in a {@link Level level} object and insert it to the database as a new row.
	 * Note: the supplied {@link Level level{ object should contain a reference to a {@link Word word} and a {@link Difficulty difficulty}.
	 */
	@Override
	public void insert(Level model) throws SQLException {
		insertStatement = connection.prepareStatement(INSERT_SQL);
		insertStatement.setInt(1, model.getWord().getId());
		insertStatement.setInt(2, model.getDifficulty().getId());
		insertStatement.setString(3, model.getTitle());
		insertStatement.executeUpdate();
		Statement statement = connection.createStatement();
		ResultSet keyGenerated = statement.executeQuery("SELECT last_insert_rowid()");
		if(keyGenerated.next()) {
			int id = keyGenerated.getInt(1);
			model.setId(id);
			objectCache.put(id, model);
		}
		keyGenerated.close();
		insertStatement.close();
		
	}
	
	/**
	 * Takes in a list of {@link Word words} and all {@link Difficulty difficulties} to dynamically creates a level of each difficulty with a word.
	 * This is useful to perform a batch level creation. 
	 * @param words -the list of words
	 * @param difficulties -the list of difficulties
	 * @throws SQLException
	 */
	public void insertLevels(List<Word> words, List<Difficulty> difficulties) throws SQLException {
		connection.setAutoCommit(false);
		insertStatement = connection.prepareStatement(INSERT_SQL);
		
		for (Difficulty difficulty : difficulties) {
			for (Word word : words) {
				insertStatement.setInt(1, word.getId());
				insertStatement.setInt(2, difficulty.getId());
				insertStatement.setString(3, word.getWord()+" - "+difficulty.getName());
				insertStatement.executeUpdate();
			}
		}
		connection.commit();
		insertStatement.close();
		connection.setAutoCommit(true);
	}
	

	@Override
	public void update(Level model) throws SQLException {
		updateStatement = connection.prepareStatement(UPDATE_SQL);
		insertStatement.setInt(1, model.getWord().getId());
		insertStatement.setInt(2, model.getDifficulty().getId());
		insertStatement.setString(3, model.getTitle());
		updateStatement.setInt(4, model.getId());
		updateStatement.executeUpdate();
		objectCache.put(model.getId(), model);
		updateStatement.close();
	}

	@Override
	public void delete(Level model) throws SQLException {
		deleteStatement = connection.prepareStatement(DELETE_SQL);
		deleteStatement.setInt(1, model.getId());
		deleteStatement.executeUpdate();
		objectCache.remove(model.getId());
		deleteStatement.close();
		
	}

	@Override
	public int count(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int count() throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(COUNT_SQL);
		resultSet.next();
		int result=resultSet.getInt(1);
		resultSet.close();
		statement.close();
		return result;
	}
	
	/**
	 * Returns a count of levels found in the supplied {@link Difficulty difficulty}.
	 * @param difficulty
	 * @return
	 * @throws SQLException
	 */
	public int count(Difficulty difficulty) throws SQLException{
		selectStatement=connection.prepareStatement(COUNT_DIFFICULTY_LEVEL_SQL);
		selectStatement.setInt(1, difficulty.getId());
		ResultSet resultSet=selectStatement.executeQuery();
		resultSet.next();
		int result=resultSet.getInt(1);
		resultSet.close();
		selectStatement.close();
		return result;
	}

	@Override
	public Level getObject(ResultSet resultSet) throws SQLException {
		
		return getObject(resultSet, false);
	}
	
	public Level getObject(ResultSet resultSet, boolean getWord) throws SQLException {
		int id=resultSet.getInt(Level.ID);
		Level model=objectCache.get(id);
		if (model==null) {
			model=new Level();
			model.setId(id);
			model=setupObject(resultSet, model,getWord);
			objectCache.put(id, model);
		}else {
			model=setupObject(resultSet, model,getWord);	
		}
		return model;
	}
	
	private Level setupObject(ResultSet resultSet, Level model, boolean getWord) throws SQLException {
		model.getWord().setId(resultSet.getInt(Level.WORD_ID));
		if (getWord) {
			model.getWord().setWord(resultSet.getString(Word.WORD));
			model.getWord().setDefinition(resultSet.getString(Word.DEFINITION));
		}
		model.getDifficulty().setId(resultSet.getInt(Level.DIFFICULTY_ID));
		model.setTitle(resultSet.getString(Level.TITLE));
		return model;
	}
	
	protected static boolean hasColumn(ResultSet resultSet, String columnName) throws SQLException{
		boolean hasColumn = false;
		  ResultSetMetaData rsmd = resultSet.getMetaData();
		  int columns = rsmd.getColumnCount();
		  for (int x = 1; x <= columns; x++) {
		    if (columnName.equals(rsmd.getColumnName(x))) {
		      hasColumn = true;
		      break;
		    }
		  }
		  return hasColumn;
	}

	@Override
	public boolean isClosed() throws SQLException {
		if (connection!=null) {
			return connection.isClosed();
		}
		return false;
	}

	@Override
	public void close() throws SQLException {
		if (connection != null) {
			if (!connection.isClosed()) {
				connection.close();
			}
			connection = null;
		}
		
	}

	@Override
	public void setConnection(Connection connection) {
		this.connection=connection;
	}

}
